package com.surprise.db.dao.join;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.surprise.db.dao.SimpleDAO;
import com.surprise.db.entity.join.SelectUserRelation002Entity;

/**
 * SelectUserRelation002.java
 * @author xuling
 * @since 2017年2月14日
 */
@Repository
public class SelectUserRelation002 extends SimpleDAO<SelectUserRelation002Entity> {

	/**
	 * 获得用户发布的所有内容
	 * @param userid 自己用户ID
	 * @param friendid 对象用户ID
	 * @param limit limit信息
	 * @return 用户发布的所有内容
	 */
	public List<SelectUserRelation002Entity> select(Long userid, Long friendid, String limit) {
		String sql = " SELECT subQuery.id, subQuery.userid, subQuery.content, subQuery.titleimg, " + 
				" subQuery.detailimg, subQuery.typeid, subQuery.addtime, " + 
				" subQuery.address, subQuery.username as name, subQuery.portrait, subQuery.mallName, " + 
				" subQuery.likeCount,subQuery.commentCount, subQuery.shareCount, " +
				" (SELECT COUNT(user_relation.id) from user_relation where user_relation.userid = ? " + 
				" and user_relation.friendid = ? and user_relation.deleteflg = 0 ) as userLikeCount " + 
				" FROM " +
				" (SELECT pm_things_mst.id, pm_things_mst.userid, pm_things_mst.content, " +
				" pm_things_mst.titleimg, pm_things_mst.detailimg, " +
				" 1 as typeid, pm_things_mst.addtime, pm_things_mst.address, " +
				" user.username, user.portrait, mall.name as mallName, " +
				" COUNT(DISTINCT pm_things_like_mst.id) as likeCount, " +
				" COUNT(DISTINCT pm_things_comment_mst.id) as commentCount, " +
				" COUNT(DISTINCT pm_things_share_mst.id) as shareCount " +
				" FROM pm_things_mst " +
				" INNER JOIN user on user.id = pm_things_mst.userid and pm_things_mst.userid = ? " +
				" LEFT JOIN mall on mall.id = pm_things_mst.mallid " +
				" LEFT JOIN pm_things_like_mst on pm_things_mst.id = pm_things_like_mst.thingsid and pm_things_like_mst.deleteflg = 0 " +
				" LEFT JOIN pm_things_comment_mst on pm_things_mst.id = pm_things_comment_mst.thingsid  " +
				" LEFT JOIN pm_things_share_mst on pm_things_mst.id = pm_things_share_mst.thingsid and pm_things_share_mst.deleteflg = 0 " +
				" Group BY pm_things_mst.content, pm_things_mst.titleimg, pm_things_mst.detailimg, pm_things_mst.addtime, pm_things_mst.address, " +
				" user.username, user.portrait, mall.name " +
				" UNION ALL " +
				" SELECT pm_help_mst.id, pm_help_mst.userid, pm_help_mst.content, pm_help_mst.titleimg, pm_help_mst.detailimg, " +
				" 2 as typeid, pm_help_mst.addtime, pm_help_mst.address, " +
				" user.username, user.portrait, mall.name as mallName, " +
				" COUNT(DISTINCT pm_help_like_mst.id) as likeCount, " +
				" COUNT(DISTINCT pm_help_comment_mst.id) as commentCount, " +
				" COUNT(DISTINCT pm_help_share_mst.id) as shareCount " +
				" FROM pm_help_mst " +
				" INNER JOIN user on user.id = pm_help_mst.userid and pm_help_mst.userid = ? " +
				" LEFT JOIN mall on mall.id = pm_help_mst.mallid " +
				" LEFT JOIN pm_help_like_mst on pm_help_mst.id = pm_help_like_mst.helpid and pm_help_like_mst.deleteflg = 0 " +
				" LEFT JOIN pm_help_comment_mst on pm_help_mst.id = pm_help_comment_mst.helpid  " +
				" LEFT JOIN pm_help_share_mst on pm_help_mst.id = pm_help_share_mst.helpid and pm_help_share_mst.deleteflg = 0 " +
				" Group BY pm_help_mst.content, pm_help_mst.titleimg, pm_help_mst.detailimg, pm_help_mst.addtime, pm_help_mst.address, " +
				" user.username, user.portrait, mall.name " +
				" ) subQuery " +
				" ORDER BY subQuery.addtime desc " + 
				limit;
		return super.findBySql(sql, userid, friendid, friendid, friendid);
	}
}
